package SampleDB;

import java.sql.*;

public class SampleDB {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	private String user_id = null;
	private String sql = null;
	
	// private CreateDB createDB = null;

	private static class SampleDBSingle {
		static final SampleDB sampleDB = new SampleDB();
	}

	public static SampleDB getInstatnce() {
		return SampleDBSingle.sampleDB;
	}

	private SampleDB() {
		String sql;

		OpenConnection();
		System.out.println("Open Connection.");
		
		CreateUserIDTable();
		CreateUserInfoTable();
	}
	
	public Connection getConnection() {
		return conn;
	}
	
	private Boolean CreateUserIDTable() {
		// Create Table USER_ID : ID, PASSWORD
		System.out.println("Create USER_ID Table.");
		sql = "CREATE TABLE USER_ID "
				+ "(ID VARCHAR(20) NOT NULL PRIMARY KEY, "
				+ "PASSWORD VARCHAR(20) NOT NULL)";
		if (CreateTable()) {
			System.out.println("Create USER_ID Table Complete.");
			return true;
		}
		return false;
	}

	private boolean CreateUserInfoTable() {
		// Create Table USER_INFO : ID, NAME, GENDER, AGE, PHONE, EMAIL, NICK
		System.out.println("Create USER_INFO Table.");
		sql = "CREATE TABLE USER_INFO "
				+ "(ID VARCHAR(20) NOT NULL PRIMARY KEY, "
				+ "NAME VARCHAR(20) NOT NULL, "
				+ "GENDER VARCHAR(5) NOT NULL, " + "AGE INT NOT NULL, "
				+ "PHONE VARCHAR(20) NOT NULL, "
				+ "EMAIL VARCHAR(30) NOT NULL, " + "NICK VARCHAR(20) NOT NULL)";
		if (CreateTable()) {
			System.out.println("Create USER_INFO Table Complete.");
			return true;
		}
		return false;
	}

	public boolean CreateFriendTable(String user_id) {
		// Create Table FRIEND : ID
		System.out.println("Create " + user_id + "_FRIEND Table.");
		sql = "CREATE TABLE " + user_id + "_FRIEND "
				+ "(ID VARCHAR(20) NOT NULL, "
				+ "PASSWORD VARCHAR(20) NOT NULL, "
				+ "CLASSIFY VARCHAR(20) NOT NULL)";
		if (CreateTable()) {
			System.out.println("Create " + user_id + "_FRIEND Table Complete.");
			return true;
		}
		return false;
	}
	
	public boolean CreateGroupTable(String user_id) {
		System.out.println("Create " + user_id + "_GROUP Table.");
		sql = "CREATE TABLE " + user_id + "_GROUP "
				+ "(GROUP_NAME VARCHAR(20) NOT NULL, "
				+ "GROUP_UP VARCHAR(20) NOT NULL, "
				+ "GROUP_LEVEL INT NOT NULL)";
		if (CreateTable()) {
			System.out.println("Create " + user_id + "_GROUP Table Complete.");
			return true;
		}
		return false;
	}

	private boolean CreateTable() {
		try {
			stmt = conn.createStatement();
			System.out.println("Create Table ...");
			stmt.execute(sql);
			stmt.close();
			return true;
		} catch (Exception e) {
			System.out.println("Can't Create Table.");
			return false;
		}
	}

	public String Select_UserID(String id) {
		try {
			String pswd;
			pstmt = conn.prepareStatement("SELECT PASSWORD FROM USER_ID WHERE ID = ?");
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getString("PASSWORD");
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public boolean Insert_UserID(String id, String pswd) {
		try {
			pstmt = conn.prepareStatement("INSERT INTO USER_ID VALUES (?, ?)");
			pstmt.setString(1, id);
			pstmt.setString(2, pswd);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean Delete_UserID(String id) {
		try {
			pstmt = conn.prepareStatement("DELETE FROM USER_ID WHERE ID = ?");
			pstmt.setString(1, id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean Update_UserID(String id, String pswd) {
		try {
			pstmt = conn
					.prepareStatement("UPDATE FROM USER_ID SET PASSWORD = ? WHERE ID = ?");
			pstmt.setString(1, pswd);
			pstmt.setString(2, id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	// public void Select_UserInfo(String id, String pswd)
	
	public boolean Insert_UserInfo(String id, String name, String gender, String age, String phone, String email, String nick) {
		try {
			pstmt = conn.prepareStatement("INSERT INTO USER_INFO VALUES (?, ?, ?, ?, ?, ?, ?)");
			pstmt.setString(1, id);
			pstmt.setString(2, name);
			pstmt.setString(3, gender);
			pstmt.setString(4, age);
			pstmt.setString(5, phone);
			pstmt.setString(6, email);
			pstmt.setString(7, nick);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	// public void Delete_UserInfo(String id, String pswd)
	// public void Update_UserInfo(String id, String pswd)

	private void OpenConnection() {
		// JavaDB Embed Driver
		String driver = "org.apache.derby.jdbc.EmbeddedDriver";

		// Database name
		String dbName = "SampleDB";

		// ConnectionURL
		String connectionURL = "jdbc:derby:" + dbName + ";create=true";

		// Load Driver
		try {
			Class.forName(driver);
		} catch (java.lang.ClassNotFoundException e) {
			e.printStackTrace();
		}

		// get Connection
		try {
			conn = DriverManager.getConnection(connectionURL);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void CloseConnection() {
		try {
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
